import json

from flask import Blueprint, render_template, request,current_app,jsonify

from flask_login import login_required, current_user
from sqlalchemy import desc,func,case

from decimal import Decimal,getcontext
# 设置精度
getcontext().prec = 16
from applications.common import curd
from applications.common.curd import enable_status, disable_status
from applications.common.utils.http import table_api, fail_api, success_api
from applications.common.utils.rights import authorize
from applications.common.utils.validate import str_escape
from applications.common.utils.getapi import get_djbh,get_isSameMonth
from applications.extensions import db
from applications.common.utils.database import query_one
from applications.models import *
from pinyin import pinyin
import datetime,os
from applications.common.utils import upload as upload_curd
bp = Blueprint('raw', __name__, url_prefix='/raw')


# 用户管理
@bp.get('/yp/')
@authorize("store:raw:main:yp")
def main():
    return render_template('store/raw/yp/main.html')


#   用户分页查询
@bp.get('/yp/data')
@authorize("store:raw:main:yp")
def data():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))
    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 简拼:
        filters.append(RawYPModel.简拼.contains(简拼))
    filters.append(StoreRawYPModel.type.in_(['入库单','其他入库单']))
    query = db.session.query(
        StoreRawYPModel
    ).join(StoreRawYPModel.raw).filter(*filters).layui_paginate()

    return table_api(
        data=[{
            'id': item.id,
            '品名': item.raw.品名,
            '简拼': item.raw.简拼,
            '产地': item.产地,
            '规格': item.规格,
            '批号': item.批号,
            '数量':item.store_number,
            '单据日期':item.date_time,
            '货位名称':item.location[0].location.name

        } for item in query.items],
        count=query.total)

    # 用户增加


@bp.get('/yp/add')
@authorize("store:raw:add:yp", log=False)
def add():
    '''先获取合格的供应商'''
    raws = RawYPModel.query.filter_by(状态=1).all()
    return render_template('store/raw/yp/add.html',goods=raws)
@bp.get('/yp/raw/<int:id>')
@authorize("store:raw:add:yp", log=False)
def getRaw(id):
    raw = RawYPModel.query.filter_by(id=id).first()
    items={'success':True,'msg':'成功'}
    items['raw']=raw.to_dict()
    items['suppliers']=[]
    for sup in raw.supperRawYp:
        if sup.状态==1:
            items['suppliers'].append(sup.to_dict())
    if not items['suppliers']:
        items['success']=False
        items['msg'] ='失败，没有找到合适供应商'

    return items



#   上传接口
@bp.post('/upload')
@authorize("store:raw:add:yp", log=True)
def upload_api():
    if 'file' in request.files:
        report = request.files['file']
        mime = request.files['file'].content_type
        upload_url = current_app.config.get("UPLOADED_STORERAWYP_DEST")

        file_url,filename = upload_curd.upload_store_raw_yp_one(report=report, mime=mime)
        size = os.path.getsize(upload_url + '/' + filename)
        res = {
            "msg": "上传成功",
            "code": 0,
            "success": True,
            "data":
                {"src": file_url,'mime':mime,'size':size,'filename':filename}
        }
        return jsonify(res)
    return fail_api()

@bp.post('/yp/save')
@authorize("store:raw:add:yp", log=True)
def save():
    req_json = request.get_json(force=True)
    fileds=['raw_id' ,'supplier_id','store_number','price','unit_id','date_time' ,
    '产地', '规格'  , '批号' ,  'user_buyer_id', 'user_keeper_id',
        'buyer_time',  'user_qa_id',   'qa_time',  'remark'
        ]
    row={}
    for filed in fileds:
        if filed in ['raw_id','supplier_id','location_id','store_number','price','unit_id','date_time' ,'产地', '规格'  , '批号' ,  'user_keeper_id']:
            if not req_json.get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get(filed):
            row[filed]=str_escape(req_json.get(filed))

    row['residual_number'] =row['store_number']
    location=[]
    location.append(StoreRawYPModel_location(
        store_number=row['residual_number'],
        stock_number=row['residual_number'],
        location_id=req_json.get('location_id'),
    ))
    fileinfo=req_json.get("fileInfo")
    files=[]
    if fileinfo:
        for file in fileinfo:
            files.append(StoreRawYPFilesModel(
                data_label=file['data']['filename'],
                href=file['data']['src'],
                mime=file['data']['mime'],
                size=file['data']['size'],

            ))
    try:

        pro=StoreRawYPModel(**row,files=files,location=location)
        db.session.add(pro)

        db.session.commit()

    except Exception as e:
        print(e)
        return fail_api(msg=f"发生错误:{e}")
    return success_api(msg="增加成功")


# 删除用户
@bp.delete('/yp/remove/<int:id>')
@authorize("store:raw:remove:yp", log=True)
def delete(id):
    upload_url = current_app.config.get("UPLOADED_STORERAWYP_DEST")
    qurey = StoreRawYPModel.query.filter_by(id=id).one()
    location=qurey.location #按货位入库

    for file in qurey.files:
        filename = file.data_label
        file_path = os.path.join(upload_url, filename)
        try:
            os.remove(file_path)
        except Exception as e:
            print(e)
        qurey.files.remove(file)
    for l in location:
        db.session.delete(l)

    db.session.delete(qurey)

    try:
        db.session.commit()
    except Exception as e:
        print(e)
        return fail_api(msg=f"删除失败:{e}")


    return success_api(msg="删除成功")


#  编辑用户
@bp.get('/yp/edit/<int:id>')
@authorize("store:raw:edit:yp", log=True)
def edit(id):
    store_raw_info = curd.get_one_by_id(StoreRawYPModel, id)
    raw_info=store_raw_info.raw
    raws = RawYPModel.query.filter_by(状态=1).all()
    suplier=store_raw_info.supplier
    locations=store_raw_info.location
    location=None
    for l in locations:
        if l.type in ['入库单','期初入库单','其他入库单'] and not l.parent_id:
            location=l.location
    user_buyer=store_raw_info.user_buyer
    user_keeper=store_raw_info.user_keeper
    user_qa=store_raw_info.user_qa
    return render_template('store/raw/yp/edit.html', info=store_raw_info,raw_info=raw_info,goods=raws,suplier=suplier,location=location,user_buyer=user_buyer,user_keeper=user_keeper,user_qa=user_qa)


#  编辑用户
@bp.put('/yp/update')
@authorize("supplier:raw:edit:yp", log=True)
def update():
    req_json = request.get_json(force=True)
    id = str_escape(req_json.get("id"))
    # username = str_escape(req_json.get('username'))
    # real_name = str_escape(req_json.get('realName'))
    # dept_id = str_escape(req_json.get('deptId'))
    fileds = ['raw_id' ,'supplier_id','store_number','price','unit_id','date_time' ,
    '产地', '规格'  , '批号' ,  'user_buyer_id', 'user_keeper_id',
        'buyer_time',  'user_qa_id',   'qa_time',  'remark']
    row = {}
    for filed in fileds:
        if filed in ['raw_id','supplier_id','location_id','store_number','price','unit_id','date_time' ,'产地', '规格'  , '批号' ,  'user_keeper_id']:
            if not req_json.get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get(filed):
            row[filed] = str_escape(req_json.get(filed))


    row['update_at'] = datetime.datetime.now()
    oldlocations = StoreRawYPModel_location.query.filter_by(store_id=id).all()
    location=[]
    for oldlocal in oldlocations:
        if oldlocal.type in ['入库单','期初入库单','其他入库单'] and not oldlocal.parent_id:
            oldlocal.stock_number=float(row['store_number']) - float(oldlocal.store_number) + float(oldlocal.stock_number)
            oldlocal.store_number = row['store_number']
            row['residual_number']=oldlocal.stock_number
            location.append(oldlocal)
            continue
        location.append(oldlocal)

    row['location'] =location

    fileinfo = req_json.get("fileInfo")
    files = []
    oldfiles=StoreRawYPFilesModel.query.filter_by(Store_raw_id=id).all()
    for oldfile in oldfiles:
        files.append(oldfile)
    if fileinfo:
        for file in fileinfo:
            files.append(StoreRawYPFilesModel(
                data_label=file['data']['filename'],
                href=file['data']['src'],
                mime=file['data']['mime'],
                size=file['data']['size'],

            ))
    row['files'] = files

    try:
        query=StoreRawYPModel.query.filter_by(id=id).one()
        for k,v in row.items():
            setattr(query, k, v) #不能写成query[k]=v 不能写成query.k=v

        db.session.commit()
    except Exception as e :
        print(e)
        return fail_api(msg=f"更新失败,{e}")

    if not query:
        return fail_api(msg="更新失败")
    return success_api(msg="更新成功")



#   用户分页查询
@bp.get('/yp/file/data')
@authorize("store:raw:main:yp")
def file_data():
    # 获取请求参数
    id_=request.args.get('id')


    query=StoreRawYPFilesModel.query.filter_by(Store_raw_id=id_).all()



    return table_api(
        data=[{


            'id': item.id,
            '文件名': item.data_label,
            '大小': item.size,




        } for item in query],
        count=len(query))

    # 用户增加



# 删除用户
@bp.delete('/yp/file/remove/<int:id>')
@authorize("store:raw:edit:yp", log=True)
def file_delete(id):
    upload_url = current_app.config.get("UPLOADED_STORERAWYP_DEST")
    qurey = StoreRawYPFilesModel.query.filter_by(id=id).one()

    filename = qurey.data_label
    file_path = os.path.join(upload_url, filename)
    try:
        os.remove(file_path)
    except Exception as e:
        print(e)


    db.session.delete(qurey)
    try:
        db.session.commit()
    except Exception as e:
        print(e)
        return fail_api(msg=f"删除失败:{e}")


    return success_api(msg="删除成功")




# 用户管理
@bp.get('/return/yp/')
@authorize("store:raw:return:main:yp")
def return_main():
    return render_template('store/raw/yp/return/main.html')

#   用户分页查询
@bp.get('/return/yp/data')
@authorize("store:raw:return:main:yp")
def return_data():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))
    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 简拼:
        filters.append(RawYPModel.简拼.contains(简拼))

    query = db.session.query(
        StoreRawYPReturnSupplierModel,
        StoreRawYPModel_location,
        StoreRawYPModel,
        RawYPModel
    ).outerjoin(StoreRawYPModel_location,StoreRawYPReturnSupplierModel.location_id==StoreRawYPModel_location.id
        ).outerjoin(StoreRawYPModel,StoreRawYPModel_location.store_id==StoreRawYPModel.id
        ).outerjoin(RawYPModel,StoreRawYPModel.raw_id==RawYPModel.id).filter(*filters).layui_paginate()

    return table_api(
        data=[{
            'id': item.StoreRawYPReturnSupplierModel.id,
            '品名': item.RawYPModel.品名,
            '简拼': item.RawYPModel.简拼,
            '产地': item.StoreRawYPModel.产地,
            '规格': item.StoreRawYPModel.规格,
            '批号': item.StoreRawYPModel.批号,
            '数量':item.StoreRawYPReturnSupplierModel.return_number,
            '单据日期': item.StoreRawYPReturnSupplierModel.date_time,
            '单位': item.StoreRawYPModel.unit.data_label


        } for item in query.items],
        count=query.total)

    # 用户增加


@bp.get('/return/yp/add')
@authorize("store:raw:return:add:yp", log=False)
def return_add():
    '''先获取合格的供应商'''

    return render_template('store/raw/yp/return/add.html')
@bp.get('/return/yp/add/frm_table')
@authorize("store:raw:return:add:yp", log=False)
def add_return_frm_table():
    '''先获取合格的供应商'''
    searchtext=request.args.get('searchtext')
    return render_template('store/raw/yp/return/add_frm_table.html',info={'searchtext':searchtext})


#   用户分页查询
@bp.get('/return/yp/searchdata')
@authorize("store:raw:return:add:yp")
def return_searchdata():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))
    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 简拼:
        filters.append(RawYPModel.简拼.contains(简拼))

    query = db.session.query(
        RawYPModel,
        StoreRawYPModel,
        StoreRawYPModel_location.stock_number,
        StoreRawYPModel_location.id,
        StoreRawYPModel_location.location_id,
        LocationRawYP.name
    ).outerjoin(RawYPModel,StoreRawYPModel.raw_id==RawYPModel.id
    ).outerjoin(StoreRawYPModel_location,StoreRawYPModel.id==StoreRawYPModel_location.store_id
    ).outerjoin(LocationRawYP,StoreRawYPModel_location.location_id==LocationRawYP.id).filter(*filters).layui_paginate()

    return table_api(
        data=[{
            'id': item.id,
            '品名': item.RawYPModel.品名,
            '简拼': item.RawYPModel.简拼,
            '产地': item.StoreRawYPModel.产地,
            '规格': item.StoreRawYPModel.规格,
            '批号': item.StoreRawYPModel.批号,
            '单位':item.StoreRawYPModel.unit.data_label,
            '退库数量': item.stock_number,
            '单据日期': item.StoreRawYPModel.date_time,
            'location_id': item.location_id,
            'location_name':item.name

        } for item in query.items],
        count=query.total)

    # 用户增加

@bp.post('/return/yp/save')
@authorize("store:raw:return:add:yp", log=True)
def return_save():
    req_json = request.get_json(force=True)

    fileds=['date_time' ,'user_keeper_id','remark']
    row={}
    for filed in fileds:
        if filed in ['date_time','user_keeper_id']:
            if not req_json.get('field').get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get('field').get(filed):
            row[filed]=str_escape(req_json.get('field').get(filed))

    row['return_dh'] =get_djbh('入库退货_yl_yp',req_json.get('field').get('date_time'))   #'CK_TH_2020_10_0006'

    try:
        isHaveDate=False

        for item in req_json.get('table_datas'):
            if item.get('id'):
                return_number=Decimal(item.get('退库数量'))
                location_id=item.get('id')
                pro=StoreRawYPReturnSupplierModel(**row,return_number=return_number,location_id=location_id)
                db.session.add(pro)
                location_item=StoreRawYPModel_location.query.filter_by(id=item.get('id')).first()
                location_item.stock_number = location_item.stock_number - return_number
                if location_item.stock_number < 0:
                    db.session.rollback()
                    return fail_api(msg=f"退货数量不能大于可退数量:{item.get('品名')} {item.get('批号')} {item.get('退库数量')}")
                isHaveDate=True
                db.session.add(location_item)
        if isHaveDate:
            db.session.commit()
        else:
            return fail_api(msg=f"发生错误:没有数据")

    except Exception as e:
        print(e)
        return fail_api(msg=f"发生错误:{e}")
    return success_api(msg="增加成功")

# 删除用户
@bp.delete('/return/yp/remove/<int:id>')
@authorize("store:raw:return:remove:yp", log=True)
def return_delete(id):

    qurey = StoreRawYPReturnSupplierModel.query.filter_by(id=id).one()

    location=qurey.location
    location.stock_number=location.stock_number+qurey.return_number

    db.session.delete(qurey)
    db.session.add(location)
    try:
        db.session.commit()
    except Exception as e:
        print(e)
        return fail_api(msg=f"删除失败:{e}")


    return success_api(msg="删除成功")


#  编辑用户
@bp.get('/return/yp/edit/<int:id>')
@authorize("store:raw:return:edit:yp", log=True)
def return_edit(id):
    # 查询 return_dh
    subquery = db.session.query(StoreRawYPReturnSupplierModel.return_dh
                                ).filter(StoreRawYPReturnSupplierModel.id == id
                                        ).subquery()

    # 使用子查询查询原料退货
    results = db.session.query(StoreRawYPReturnSupplierModel
                               ).filter(StoreRawYPReturnSupplierModel.return_dh.in_(subquery)).all()
    info={}
    info['date_time'] = results[0].date_time
    info['return_dh'] = results[0].return_dh
    info['remark'] = results[0].remark
    user_keeper= results[0].user_keeper
    rowdatas=[]
    for result in results:
        print(result)
        rowdata={
            'return_number':result.return_number,
            'id': result.location.id,
            '品名': result.location.storeRawYp.raw.品名,
            '简拼': result.location.storeRawYp.raw.简拼,
            '产地':  result.location.storeRawYp.产地,
            '规格': result.location.storeRawYp.规格,
            '批号': result.location.storeRawYp.批号,
            '单位': result.location.storeRawYp.unit.data_label,
            '退库数量': result.return_number,
            'return_id':result.id,
            'location_id': result.location.location.id,
            'location_name': result.location.location.name
        }
        rowdatas.append(rowdata)

    return render_template('store/raw/yp/return/edit.html',results=results,info=info,user_keeper=user_keeper,rowdatas=rowdatas)

#  编辑用户
@bp.put('/return/yp/update')
@authorize("supplier:raw:return:edit:yp", log=True)
def return_update():
    req_json = request.get_json(force=True)

    date_time = str_escape(req_json.get("field").get('date_time'))
    user_keeper_id = str_escape(req_json.get("field").get('user_keeper_id'))
    return_dh = str_escape(req_json.get("field").get('return_dh'))
    remark = str_escape(req_json.get("field").get('remark'))
    update_at= datetime.datetime.now()
    old_results = StoreRawYPReturnSupplierModel.query.filter_by(return_dh=return_dh).all()
    new_rusults=list(req_json.get("table_datas"))

    '''先判断data_time 月份与编号月份是否一致'''
    if not get_isSameMonth(date_time, return_dh):
        return_dh = get_djbh('入库退货_yl_yp', req_json.get('field').get('date_time'))
    for old_result in old_results:
        isHaveOldData=False #是否有就数据
        for new_result in new_rusults:
            if new_result.get('return_id') and old_result.id==new_result.get('return_id'):
                isHaveOldData=True
                old_result.date_time = date_time
                old_result.return_dh = return_dh
                old_result.remark = remark
                old_result.user_keeper_id = user_keeper_id
                old_result.update_at = update_at
                if old_result.return_number==Decimal(new_result.get('退库数量')):
                    db.session.add(old_result)
                else:

                    location_item = StoreRawYPModel_location.query.filter_by(id=old_result.location_id).first()
                    location_item.stock_number = location_item.stock_number + old_result.return_number - Decimal(new_result.get('退库数量'))
                    old_result.return_number = new_result.get('退库数量')
                    if location_item.stock_number < 0:
                        db.session.rollback()
                        return fail_api(
                            msg=f"退货数量不能大于可退数量:{new_result.get('品名')} {new_result.get('批号')} {new_result.get('退库数量')}")
                    db.session.add(old_result)
                    db.session.add(location_item)

                break
        if not isHaveOldData:
            location_item = StoreRawYPModel_location.query.filter_by(id=old_result.location_id).first()
            location_item.stock_number = location_item.stock_number + old_result.return_number

            if location_item.stock_number < 0:
                db.session.rollback()
                return fail_api(
                    msg=f"退货数量不能大于可退数量:{old_result.id}")
            db.session.delete(old_result)
            db.session.add(location_item)
    for new_result in new_rusults:
        if not new_result.get('return_id') and len(new_result)>3: #新单子
            pro = StoreRawYPReturnSupplierModel()
            pro.date_time = date_time
            pro.return_dh = return_dh
            pro.remark = remark
            pro.user_keeper_id = user_keeper_id
            pro.update_at = update_at
            pro.return_number = Decimal(new_result.get('退库数量'))
            pro.location_id = new_result.get('id')
            db.session.add(pro)
            location_item = StoreRawYPModel_location.query.filter_by(id=new_result.get('id')).first()
            location_item.stock_number = location_item.stock_number - pro.return_number
            if location_item.stock_number < 0:
                db.session.rollback()
                return fail_api(
                    msg=f"退货数量不能大于可退数量:{new_result.get('品名')} {new_result.get('批号')} {new_result.get('退库数量')}")

            db.session.add(location_item)




    try:

        db.session.commit()
    except Exception as e :
        print(e)
        return fail_api(msg=f"更新失败,{e}")


    return success_api(msg="更新成功")




# 报损管理
@bp.get('/loss/yp/')
@authorize("store:raw:loss:main:yp")
def loss_main():
    return render_template('store/raw/yp/loss/main.html')


#   用户分页查询
@bp.get('/loss/yp/data')
@authorize("store:raw:loss:main:yp")
def loss_data():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))
    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 简拼:
        filters.append(RawYPModel.简拼.contains(简拼))

    query = db.session.query(
        StoreRawYPLossModel,
        StoreRawYPModel_location,
        StoreRawYPModel,
        RawYPModel
    ).outerjoin(StoreRawYPModel_location,StoreRawYPLossModel.location_id==StoreRawYPModel_location.id
        ).outerjoin(StoreRawYPModel,StoreRawYPModel_location.store_id==StoreRawYPModel.id
        ).outerjoin(RawYPModel,StoreRawYPModel.raw_id==RawYPModel.id).filter(*filters).layui_paginate()

    return table_api(
        data=[{
            'id': item.StoreRawYPLossModel.id,
            '品名': item.RawYPModel.品名,
            '简拼': item.RawYPModel.简拼,
            '产地': item.StoreRawYPModel.产地,
            '规格': item.StoreRawYPModel.规格,
            '批号': item.StoreRawYPModel.批号,
            '数量':item.StoreRawYPLossModel.loss_number,
            '单据日期': item.StoreRawYPLossModel.date_time,
            '单位': item.StoreRawYPModel.unit.data_label


        } for item in query.items],
        count=query.total)

    # 用户增加


@bp.get('/loss/yp/add')
@authorize("store:raw:loss:add:yp", log=False)
def loss_add():
    '''先获取合格的供应商'''

    return render_template('store/raw/yp/loss/add.html')

#   用户分页查询
@bp.get('/loss/yp/searchdata')
@authorize("store:raw:loss:add:yp")
def loss_searchdata():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))
    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 简拼:
        filters.append(RawYPModel.简拼.contains(简拼))

    query = db.session.query(
        RawYPModel,
        StoreRawYPModel,
        StoreRawYPModel_location.stock_number,
        StoreRawYPModel_location.id,
        StoreRawYPModel_location.location_id,
        LocationRawYP.name
    ).outerjoin(RawYPModel,StoreRawYPModel.raw_id==RawYPModel.id
    ).outerjoin(StoreRawYPModel_location,StoreRawYPModel.id==StoreRawYPModel_location.store_id
    ).outerjoin(LocationRawYP,StoreRawYPModel_location.location_id==LocationRawYP.id).filter(*filters).layui_paginate()

    return table_api(
        data=[{
            'id': item.id,
            '品名': item.RawYPModel.品名,
            '简拼': item.RawYPModel.简拼,
            '产地': item.StoreRawYPModel.产地,
            '规格': item.StoreRawYPModel.规格,
            '批号': item.StoreRawYPModel.批号,
            '单位':item.StoreRawYPModel.unit.data_label,
            '可报损数量': item.stock_number,
            '单据日期': item.StoreRawYPModel.date_time,
            'location_id': item.location_id,
            'location_name':item.name

        } for item in query.items],
        count=query.total)

    # 用户增加
@bp.get('/loss/yp/add/frm_table')
@authorize("store:raw:loss:add:yp", log=False)
def add_loss_frm_table():
    '''先获取合格的供应商'''
    searchtext=request.args.get('searchtext')
    return render_template('store/raw/yp/loss/add_frm_table.html',info={'searchtext':searchtext})

@bp.post('/loss/yp/save')
@authorize("store:raw:loss:add:yp", log=True)
def loss_save():
    req_json = request.get_json(force=True)

    fileds=['date_time' ,'user_keeper_id','remark']
    row={}
    for filed in fileds:
        if filed in ['date_time','user_keeper_id']:
            if not req_json.get('field').get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get('field').get(filed):
            row[filed]=str_escape(req_json.get('field').get(filed))

    row['loss_dh'] =get_djbh('仓库报损_yl_yp',req_json.get('field').get('date_time'))   #'CK_TH_2020_10_0006'

    try:
        isHaveDate=False

        for item in req_json.get('table_datas'):
            if item.get('id'):
                return_number=Decimal(item.get('可报损数量'))
                location_id=item.get('id')
                pro=StoreRawYPLossModel(**row,loss_number=return_number,location_id=location_id)
                db.session.add(pro)
                location_item=StoreRawYPModel_location.query.filter_by(id=item.get('id')).first()
                location_item.stock_number = location_item.stock_number - return_number
                if location_item.stock_number < 0:
                    db.session.rollback()
                    return fail_api(msg=f"报损数量不能大于库存量量:{item.get('品名')} {item.get('批号')} {item.get('可报损数量')}")
                isHaveDate=True
                db.session.add(location_item)
        if isHaveDate:
            db.session.commit()
        else:
            return fail_api(msg=f"发生错误:没有数据")

    except Exception as e:
        print(e)
        return fail_api(msg=f"发生错误:{e}")
    return success_api(msg="增加成功")


# 删除报损数量
@bp.delete('/loss/yp/remove/<int:id>')
@authorize("store:raw:loss:remove:yp", log=True)
def loss_delete(id):

    qurey = StoreRawYPLossModel.query.filter_by(id=id).one()

    location=qurey.location
    location.stock_number=location.stock_number+qurey.loss_number

    db.session.delete(qurey)
    db.session.add(location)
    try:
        db.session.commit()
    except Exception as e:
        print(e)
        return fail_api(msg=f"删除失败:{e}")


    return success_api(msg="删除成功")


#  编辑用户
@bp.get('/loss/yp/edit/<int:id>')
@authorize("store:raw:loss:edit:yp", log=True)
def loss_edit(id):
    # 查询 return_dh
    subquery = db.session.query(StoreRawYPLossModel.loss_dh
                                ).filter(StoreRawYPLossModel.id == id
                                        ).subquery()

    # 使用子查询查询原料退货
    results = db.session.query(StoreRawYPLossModel
                               ).filter(StoreRawYPLossModel.loss_dh.in_(subquery)).all()
    info={}
    info['date_time'] = results[0].date_time
    info['loss_dh'] = results[0].loss_dh
    info['remark'] = results[0].remark
    user_keeper= results[0].user_keeper
    rowdatas=[]
    for result in results:
        print(result)
        rowdata={
            'loss_number':result.loss_number,
            'id': result.location.id,
            '品名': result.location.storeRawYp.raw.品名,
            '简拼': result.location.storeRawYp.raw.简拼,
            '产地':  result.location.storeRawYp.产地,
            '规格': result.location.storeRawYp.规格,
            '批号': result.location.storeRawYp.批号,
            '单位': result.location.storeRawYp.unit.data_label,
            '可报损数量': result.loss_number,
            'loss_id':result.id,
            'location_id': result.location.location.id,
            'location_name': result.location.location.name
        }
        rowdatas.append(rowdata)

    return render_template('store/raw/yp/loss/edit.html',results=results,info=info,user_keeper=user_keeper,rowdatas=rowdatas)


#  编辑用户
@bp.put('/loss/yp/update')
@authorize("supplier:raw:loss:edit:yp", log=True)
def loss_update():
    req_json = request.get_json(force=True)

    date_time = str_escape(req_json.get("field").get('date_time'))
    user_keeper_id = str_escape(req_json.get("field").get('user_keeper_id'))
    loss_dh = str_escape(req_json.get("field").get('loss_dh'))
    remark = str_escape(req_json.get("field").get('remark'))
    update_at= datetime.datetime.now()
    old_results = StoreRawYPLossModel.query.filter_by(loss_dh=loss_dh).all()
    new_rusults=list(req_json.get("table_datas"))

    '''先判断data_time 月份与编号月份是否一致'''
    if not get_isSameMonth(date_time, loss_dh):
        return_dh = get_djbh('仓库报损_yl_yp', req_json.get('field').get('date_time'))
    for old_result in old_results:
        isHaveOldData=False #是否有就数据
        for new_result in new_rusults:
            if new_result.get('loss_id') and old_result.id==new_result.get('loss_id'):
                isHaveOldData=True
                old_result.date_time = date_time
                old_result.return_dh = loss_dh
                old_result.remark = remark
                old_result.user_keeper_id = user_keeper_id
                old_result.update_at = update_at
                if old_result.loss_number==Decimal(new_result.get('可报损数量')):
                    db.session.add(old_result)
                else:

                    location_item = StoreRawYPModel_location.query.filter_by(id=old_result.location_id).first()
                    location_item.stock_number = location_item.stock_number + old_result.loss_number - Decimal(new_result.get('可报损数量'))
                    old_result.loss_number = new_result.get('可报损数量')
                    if location_item.stock_number < 0:
                        db.session.rollback()
                        return fail_api(
                            msg=f"退货数量不能大于可退数量:{new_result.get('品名')} {new_result.get('批号')} {new_result.get('可报损数量')}")
                    db.session.add(old_result)
                    db.session.add(location_item)

                break
        if not isHaveOldData:
            location_item = StoreRawYPModel_location.query.filter_by(id=old_result.location_id).first()
            location_item.stock_number = location_item.stock_number + old_result.loss_number

            if location_item.stock_number < 0:
                db.session.rollback()
                return fail_api(
                    msg=f"报损数量不能大于库存数量:{old_result.id}")
            db.session.delete(old_result)
            db.session.add(location_item)
    for new_result in new_rusults:
        if not new_result.get('loss_id') and len(new_result)>3: #新单子
            pro = StoreRawYPLossModel()
            pro.date_time = date_time
            pro.loss_dh = loss_dh
            pro.remark = remark
            pro.user_keeper_id = user_keeper_id
            pro.update_at = update_at
            pro.loss_number = Decimal(new_result.get('可报损数量'))
            pro.location_id = new_result.get('id')
            db.session.add(pro)
            location_item = StoreRawYPModel_location.query.filter_by(id=new_result.get('id')).first()
            location_item.stock_number = location_item.stock_number - pro.loss_number
            if location_item.stock_number < 0:
                db.session.rollback()
                return fail_api(
                    msg=f"报损数量不能大于库存数量:{new_result.get('品名')} {new_result.get('批号')} {new_result.get('可报损数量')}")

            db.session.add(location_item)




    try:

        db.session.commit()
    except Exception as e :
        print(e)
        return fail_api(msg=f"更新失败,{e}")


    return success_api(msg="更新成功")



# 报溢管理
@bp.get('/excess/yp/')
@authorize("store:raw:excess:main:yp")
def excess_main():
    return render_template('store/raw/yp/excess/main.html')


#   用户分页查询
@bp.get('/excess/yp/data')
@authorize("store:raw:excess:main:yp")
def excess_data():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))
    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 简拼:
        filters.append(RawYPModel.简拼.contains(简拼))

    query = db.session.query(
        StoreRawYPExcessModel,
        StoreRawYPModel_location,
        StoreRawYPModel,
        RawYPModel
    ).outerjoin(StoreRawYPModel_location,StoreRawYPExcessModel.location_id==StoreRawYPModel_location.id
        ).outerjoin(StoreRawYPModel,StoreRawYPModel_location.store_id==StoreRawYPModel.id
        ).outerjoin(RawYPModel,StoreRawYPModel.raw_id==RawYPModel.id).filter(*filters).layui_paginate()

    return table_api(
        data=[{
            'id': item.StoreRawYPExcessModel.id,
            '品名': item.RawYPModel.品名,
            '简拼': item.RawYPModel.简拼,
            '产地': item.StoreRawYPModel.产地,
            '规格': item.StoreRawYPModel.规格,
            '批号': item.StoreRawYPModel.批号,
            '数量':item.StoreRawYPExcessModel.excess_number,
            '单据日期': item.StoreRawYPExcessModel.date_time,
            '单位': item.StoreRawYPModel.unit.data_label


        } for item in query.items],
        count=query.total)

    # 用户增加

@bp.get('/excess/yp/add')
@authorize("store:raw:excess:add:yp", log=False)
def excess_add():
    '''先获取合格的供应商'''

    return render_template('store/raw/yp/excess/add.html')

#   用户分页查询
@bp.get('/excess/yp/searchdata')
@authorize("store:raw:excess:add:yp")
def excess_searchdata():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))
    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 简拼:
        filters.append(RawYPModel.简拼.contains(简拼))

    query = db.session.query(
        RawYPModel,
        StoreRawYPModel,
        StoreRawYPModel_location.stock_number,
        StoreRawYPModel_location.id,
        StoreRawYPModel_location.location_id,
        LocationRawYP.name
    ).outerjoin(RawYPModel,StoreRawYPModel.raw_id==RawYPModel.id
    ).outerjoin(StoreRawYPModel_location,StoreRawYPModel.id==StoreRawYPModel_location.store_id
    ).outerjoin(LocationRawYP,StoreRawYPModel_location.location_id==LocationRawYP.id).filter(*filters).layui_paginate()

    return table_api(
        data=[{
            'id': item.id,
            '品名': item.RawYPModel.品名,
            '简拼': item.RawYPModel.简拼,
            '产地': item.StoreRawYPModel.产地,
            '规格': item.StoreRawYPModel.规格,
            '批号': item.StoreRawYPModel.批号,
            '单位':item.StoreRawYPModel.unit.data_label,
            '报溢数量': item.stock_number,
            '单据日期': item.StoreRawYPModel.date_time,
            'location_id': item.location_id,
            'location_name':item.name

        } for item in query.items],
        count=query.total)

    # 用户增加
@bp.get('/excess/yp/add/frm_table')
@authorize("store:raw:excess:add:yp", log=False)
def add_excess_frm_table():
    '''先获取合格的供应商'''
    searchtext=request.args.get('searchtext')
    return render_template('store/raw/yp/excess/add_frm_table.html',info={'searchtext':searchtext})

@bp.post('/excess/yp/save')
@authorize("store:raw:excess:add:yp", log=True)
def excess_save():
    req_json = request.get_json(force=True)

    fileds=['date_time' ,'user_keeper_id','remark']
    row={}
    for filed in fileds:
        if filed in ['date_time','user_keeper_id']:
            if not req_json.get('field').get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get('field').get(filed):
            row[filed]=str_escape(req_json.get('field').get(filed))

    row['excess_dh'] =get_djbh('仓库报溢_yl_yp',req_json.get('field').get('date_time'))   #'CK_TH_2020_10_0006'

    try:
        isHaveDate=False

        for item in req_json.get('table_datas'):
            if item.get('id'):
                return_number=Decimal(item.get('报溢数量'))
                location_id=item.get('id')
                pro=StoreRawYPExcessModel(**row,excess_number=return_number,location_id=location_id)
                db.session.add(pro)
                location_item=StoreRawYPModel_location.query.filter_by(id=item.get('id')).first()
                location_item.stock_number = location_item.stock_number + return_number
                # if location_item.stock_number < 0:
                #     db.session.rollback()
                #     return fail_api(msg=f"报溢数量不能大于库存量量:{item.get('品名')} {item.get('批号')} {item.get('可报损数量')}")
                isHaveDate=True
                db.session.add(location_item)
        if isHaveDate:
            db.session.commit()
        else:
            return fail_api(msg=f"发生错误:没有数据")

    except Exception as e:
        print(e)
        return fail_api(msg=f"发生错误:{e}")
    return success_api(msg="增加成功")



# 删除报损数量
@bp.delete('/excess/yp/remove/<int:id>')
@authorize("store:raw:excess:remove:yp", log=True)
def excess_delete(id):

    qurey = StoreRawYPExcessModel.query.filter_by(id=id).one()

    location=qurey.location
    location.stock_number=location.stock_number-qurey.excess_number

    db.session.delete(qurey)
    db.session.add(location)
    try:
        db.session.commit()
    except Exception as e:
        print(e)
        return fail_api(msg=f"删除失败:{e}")


    return success_api(msg="删除成功")


#  编辑用户
@bp.get('/excess/yp/edit/<int:id>')
@authorize("store:raw:excess:edit:yp", log=True)
def excess_edit(id):
    # 查询 return_dh
    subquery = db.session.query(StoreRawYPExcessModel.excess_dh
                                ).filter(StoreRawYPExcessModel.id == id
                                        ).subquery()

    # 使用子查询查询原料退货
    results = db.session.query(StoreRawYPExcessModel
                               ).filter(StoreRawYPExcessModel.excess_dh.in_(subquery)).all()
    info={}
    info['date_time'] = results[0].date_time
    info['excess_dh'] = results[0].excess_dh
    info['remark'] = results[0].remark
    user_keeper= results[0].user_keeper
    rowdatas=[]
    for result in results:
        print(result)
        rowdata={
            'excess_number':result.excess_number,
            'id': result.location.id,
            '品名': result.location.storeRawYp.raw.品名,
            '简拼': result.location.storeRawYp.raw.简拼,
            '产地':  result.location.storeRawYp.产地,
            '规格': result.location.storeRawYp.规格,
            '批号': result.location.storeRawYp.批号,
            '单位': result.location.storeRawYp.unit.data_label,
            '报溢数量': result.excess_number,
            'excess_id':result.id,
            'location_id': result.location.location.id,
            'location_name': result.location.location.name
        }
        rowdatas.append(rowdata)

    return render_template('store/raw/yp/excess/edit.html',results=results,info=info,user_keeper=user_keeper,rowdatas=rowdatas)


#  编辑用户
@bp.put('/excess/yp/update')
@authorize("supplier:raw:excess:edit:yp", log=True)
def excess_update():
    req_json = request.get_json(force=True)

    date_time = str_escape(req_json.get("field").get('date_time'))
    user_keeper_id = str_escape(req_json.get("field").get('user_keeper_id'))
    excess_dh = str_escape(req_json.get("field").get('excess_dh'))
    remark = str_escape(req_json.get("field").get('remark'))
    update_at= datetime.datetime.now()
    old_results = StoreRawYPExcessModel.query.filter_by(excess_dh=excess_dh).all()
    new_rusults=list(req_json.get("table_datas"))

    '''先判断data_time 月份与编号月份是否一致'''
    if not get_isSameMonth(date_time, excess_dh):
        excess_dh = get_djbh('仓库报溢_yl_yp', req_json.get('field').get('date_time'))
    for old_result in old_results:
        isHaveOldData=False #是否有就数据
        for new_result in new_rusults:
            if new_result.get('excess_id') and old_result.id==new_result.get('excess_id'):
                isHaveOldData=True
                old_result.date_time = date_time
                old_result.return_dh = excess_dh
                old_result.remark = remark
                old_result.user_keeper_id = user_keeper_id
                old_result.update_at = update_at
                if old_result.excess_number==Decimal(new_result.get('报溢数量')):
                    db.session.add(old_result)
                else:

                    location_item = StoreRawYPModel_location.query.filter_by(id=old_result.location_id).first()
                    location_item.stock_number = location_item.stock_number - old_result.excess_number + Decimal(new_result.get('报溢数量'))
                    old_result.excess_number = new_result.get('报溢数量')
                    if location_item.stock_number < 0:
                        db.session.rollback()
                        return fail_api(
                            msg=f"报溢后库存量要大于0:{new_result.get('品名')} {new_result.get('批号')} {new_result.get('可报损数量')}")
                    db.session.add(old_result)
                    db.session.add(location_item)

                break
        if not isHaveOldData:
            location_item = StoreRawYPModel_location.query.filter_by(id=old_result.location_id).first()
            location_item.stock_number = location_item.stock_number - old_result.excess_number

            if location_item.stock_number < 0:
                db.session.rollback()
                return fail_api(
                    msg=f"报溢后数量要大于0:{old_result.id}")
            db.session.delete(old_result)
            db.session.add(location_item)
    for new_result in new_rusults:
        if not new_result.get('excess_id') and len(new_result)>3: #新单子
            pro = StoreRawYPExcessModel()
            pro.date_time = date_time
            pro.excess_dh = excess_dh
            pro.remark = remark
            pro.user_keeper_id = user_keeper_id
            pro.update_at = update_at
            pro.excess_number = Decimal(new_result.get('报溢数量'))
            pro.location_id = new_result.get('id')
            db.session.add(pro)
            location_item = StoreRawYPModel_location.query.filter_by(id=new_result.get('id')).first()
            location_item.stock_number = location_item.stock_number + pro.excess_number
            if location_item.stock_number < 0:
                db.session.rollback()
                return fail_api(
                    msg=f"报溢后库存数量要大于0:{new_result.get('品名')} {new_result.get('批号')} {new_result.get('报溢数量')}")

            db.session.add(location_item)




    try:

        db.session.commit()
    except Exception as e :
        print(e)
        return fail_api(msg=f"更新失败,{e}")


    return success_api(msg="更新成功")


# 销售管理
@bp.get('/sold/yp/')
@authorize("store:raw:sold:main:yp")
def send_main():
    return render_template('store/raw/yp/sold/main.html')



#   用户分页查询
@bp.get('/sold/yp/data')
@authorize("store:raw:sold:main:yp")
def sold_data():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))
    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 简拼:
        filters.append(RawYPModel.简拼.contains(简拼))

    query = db.session.query(
        StoreRawYPExcessModel,
        StoreRawYPModel_location,
        StoreRawYPModel,
        RawYPModel
    ).outerjoin(StoreRawYPModel_location,StoreRawYPExcessModel.location_id==StoreRawYPModel_location.id
        ).outerjoin(StoreRawYPModel,StoreRawYPModel_location.store_id==StoreRawYPModel.id
        ).outerjoin(RawYPModel,StoreRawYPModel.raw_id==RawYPModel.id).filter(*filters).layui_paginate()

    return table_api(
        data=[{
            'id': item.StoreRawYPExcessModel.id,
            '品名': item.RawYPModel.品名,
            '简拼': item.RawYPModel.简拼,
            '产地': item.StoreRawYPModel.产地,
            '规格': item.StoreRawYPModel.规格,
            '批号': item.StoreRawYPModel.批号,
            '数量':item.StoreRawYPExcessModel.excess_number,
            '单据日期': item.StoreRawYPExcessModel.date_time,
            '单位': item.StoreRawYPModel.unit.data_label


        } for item in query.items],
        count=query.total)

    # 用户增加


# 期初
@bp.get('/begin/yp/')
@authorize("store:raw:begin:main:yp")
def begin_main():
    return render_template('store/raw/yp/begin/main.html')


#   用户分页查询
@bp.get('/begin/yp/data')
@authorize("store:raw:begin:main:yp")
def begin_data():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))
    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 简拼:
        filters.append(RawYPModel.简拼.contains(简拼))
    filters.append(StoreRawYPModel.type.contains('期初入库单'))
    query = db.session.query(
        StoreRawYPModel
    ).join(StoreRawYPModel.raw).filter(*filters).layui_paginate()

    return table_api(
        data=[{
            'id': item.id,
            '品名': item.raw.品名,
            '简拼': item.raw.简拼,
            '产地': item.产地,
            '规格': item.规格,
            '批号': item.批号,
            '数量':item.store_number,
            '单据日期':item.date_time,
            '货位名称': item.location[0].location.name
        } for item in query.items],
        count=query.total)

@bp.get('/begin/yp/add')
@authorize("store:raw:begin:add:yp", log=False)
def begin_add():
    '''先获取合格的供应商'''
    raws = RawYPModel.query.filter_by(状态=1).all()
    return render_template('store/raw/yp/begin/add.html', goods=raws)

@bp.get('/begin/yp/raw/<int:id>')
@authorize("store:raw:begin:add:yp", log=False)
def begin_getRaw(id):
    raw = RawYPModel.query.filter_by(id=id).first()
    items = {'success': True, 'msg': '成功'}
    items['raw'] = raw.to_dict()
    items['suppliers'] = []
    # for sup in raw.supperRawYp:
    #     if sup.状态 == 1:
    #         items['suppliers'].append(sup.to_dict())
    # if not items['suppliers']:
    #     items['success'] = False
    #     items['msg'] = '失败，没有找到合适供应商'

    return items

@bp.post('/begin/yp/save')
@authorize("store:raw:begin:add:yp", log=True)
def begin_save():
    req_json = request.get_json(force=True)
    fileds=['raw_id' ,'supplier_id','store_number','price','unit_id','date_time' ,
    '产地', '规格'  , '批号' ,  'user_buyer_id', 'user_keeper_id',
        'buyer_time',  'user_qa_id',   'qa_time',  'remark'
        ]
    row={}
    for filed in fileds:
        if filed in ['raw_id','location_id','store_number','price','unit_id','date_time' ,'产地', '规格'  , '批号' ,  'user_keeper_id']:
            if not req_json.get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get(filed):
            row[filed]=str_escape(req_json.get(filed))

    row['residual_number'] =row['store_number']
    row['type']='期初入库单'
    location=[]
    location.append(StoreRawYPModel_location(
        store_number=row['residual_number'],
        stock_number=row['residual_number'],
        location_id=req_json.get('location_id'),
    ))
    fileinfo=req_json.get("fileInfo")
    files=[]
    if fileinfo:
        for file in fileinfo:
            files.append(StoreRawYPFilesModel(
                data_label=file['data']['filename'],
                href=file['data']['src'],
                mime=file['data']['mime'],
                size=file['data']['size'],

            ))
    try:

        pro=StoreRawYPModel(**row,files=files,location=location)
        db.session.add(pro)

        db.session.commit()

    except Exception as e:
        print(e)
        return fail_api(msg=f"发生错误:{e}")
    return success_api(msg="增加成功")

# 删除用户
@bp.delete('/begin/yp/remove/<int:id>')
@authorize("store:raw:begin:remove:yp", log=True)
def begin_delete(id):
    upload_url = current_app.config.get("UPLOADED_STORERAWYP_DEST")
    qurey = StoreRawYPModel.query.filter_by(id=id).one()
    location=qurey.location #按货位入库

    for file in qurey.files:
        filename = file.data_label
        file_path = os.path.join(upload_url, filename)
        try:
            os.remove(file_path)
        except Exception as e:
            print(e)
        qurey.files.remove(file)
    for l in location:
        db.session.delete(l)

    db.session.delete(qurey)

    try:
        db.session.commit()
    except Exception as e:
        print(e)
        return fail_api(msg=f"删除失败:{e}")


    return success_api(msg="删除成功")


#  编辑用户
@bp.get('/begin/yp/edit/<int:id>')
@authorize("store:raw:begin:edit:yp", log=True)
def begin_edit(id):
    store_raw_info = curd.get_one_by_id(StoreRawYPModel, id)
    raw_info=store_raw_info.raw
    raws = RawYPModel.query.filter_by(状态=1).all()
    suplier=store_raw_info.supplier
    locations=store_raw_info.location
    location=None
    for l in locations:
        if l.type in ['入库单','期初入库单','其他入库单'] and not l.parent_id:
            location=l.location
    user_buyer=store_raw_info.user_buyer
    user_keeper=store_raw_info.user_keeper
    user_qa=store_raw_info.user_qa
    return render_template('store/raw/yp/begin/edit.html', info=store_raw_info,raw_info=raw_info,goods=raws,suplier=suplier,location=location,user_buyer=user_buyer,user_keeper=user_keeper,user_qa=user_qa)


#  编辑用户
@bp.put('/begin/yp/update')
@authorize("store:raw:begin:edit:yp", log=True)
def begin_update():
    req_json = request.get_json(force=True)
    id = str_escape(req_json.get("id"))
    # username = str_escape(req_json.get('username'))
    # real_name = str_escape(req_json.get('realName'))
    # dept_id = str_escape(req_json.get('deptId'))
    fileds = ['raw_id' ,'supplier_id','store_number','price','unit_id','date_time' ,
    '产地', '规格'  , '批号' ,  'user_buyer_id', 'user_keeper_id',
        'buyer_time',  'user_qa_id',   'qa_time',  'remark']
    row = {}
    for filed in fileds:
        if filed in ['raw_id','location_id','store_number','price','unit_id','date_time' ,'产地', '规格'  , '批号' ,  'user_keeper_id']:
            if not req_json.get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get(filed):
            row[filed] = str_escape(req_json.get(filed))


    row['update_at'] = datetime.datetime.now()
    oldlocations = StoreRawYPModel_location.query.filter_by(store_id=id).all()
    location=[]
    for oldlocal in oldlocations:
        if oldlocal.type in ['入库单','期初入库单','其他入库单'] and not oldlocal.parent_id:
            oldlocal.stock_number=float(row['store_number']) - float(oldlocal.store_number) + float(oldlocal.stock_number)
            oldlocal.store_number = row['store_number']
            row['residual_number']=oldlocal.stock_number
            location.append(oldlocal)
            continue
        location.append(oldlocal)

    row['location'] =location

    fileinfo = req_json.get("fileInfo")
    files = []
    oldfiles=StoreRawYPFilesModel.query.filter_by(Store_raw_id=id).all()
    for oldfile in oldfiles:
        files.append(oldfile)
    if fileinfo:
        for file in fileinfo:
            files.append(StoreRawYPFilesModel(
                data_label=file['data']['filename'],
                href=file['data']['src'],
                mime=file['data']['mime'],
                size=file['data']['size'],

            ))
    row['files'] = files

    try:
        query=StoreRawYPModel.query.filter_by(id=id).one()
        for k,v in row.items():
            setattr(query, k, v) #不能写成query[k]=v 不能写成query.k=v

        db.session.commit()
    except Exception as e :
        print(e)
        return fail_api(msg=f"更新失败,{e}")

    if not query:
        return fail_api(msg="更新失败")
    return success_api(msg="更新成功")



# 请验
@bp.get('/verify/yp/')
@authorize("store:raw:verify:main:yp")
def verify_main():
    return render_template('store/raw/yp/verify/main.html')


#   用户分页查询
@bp.get('/verify/yp/data')
@authorize("store:raw:verify:main:yp")
def verify_data():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))

    t1=db.session.query(VerifyRawYPModel,StoreRawYPModel_location.store_id).outerjoin(StoreRawYPModel_location,VerifyRawYPModel.store_location_id==StoreRawYPModel_location.id).subquery()
    t2=db.session.query(t1,StoreRawYPModel.产地,StoreRawYPModel.规格
                        ,StoreRawYPModel.批号
                        ,StoreRawYPModel.unit_id
                        ,StoreRawYPModel.raw_id
                        ).outerjoin(StoreRawYPModel,t1.c.store_id==StoreRawYPModel.id).subquery()
    t3=db.session.query(t2,case(
                            (DictData.data_label=='Kg',DictData.data_label),
                             (DictData.data_label == '条', DictData.data_label),
                             (DictData.data_label == '个', DictData.data_label),
                             else_='Kg'
                         ).label('data_label'),DictData.data_rel

                        ).outerjoin(DictData,t2.c.unit_id==DictData.id).subquery()
    t4=db.session.query(t3,RawYPModel.品名,RawYPModel.简拼
                        ).outerjoin(RawYPModel,t3.c.raw_id==RawYPModel.id).subquery()
    query_sub_verify=db.session.query(t4,db.func.sum(t4.c.verify_number * t4.c.data_rel).label('verify_number_sum')
                        ).group_by(t4.c.verify_dh).filter(t4.c.品名.contains(品名),t4.c.简拼.contains(简拼)).subquery()

    ta_1=db.session.query(VerifyRawYPModelSample,RawYPModel.品名,RawYPModel.简拼).outerjoin(RawYPModel,VerifyRawYPModelSample.raw_id==RawYPModel.id).subquery()
    query_sub_verify_sample = db.session.query(
        ta_1
    ).filter(ta_1.c.品名.contains(品名),ta_1.c.简拼.contains(简拼)).subquery()
    query=db.session.query(
        query_sub_verify.c.id,
        query_sub_verify.c.品名,
        query_sub_verify.c.简拼,
        query_sub_verify.c.产地,
        query_sub_verify.c.规格,
        query_sub_verify.c.批号,
        query_sub_verify.c.verify_number_sum,
        query_sub_verify.c.data_label,
        query_sub_verify.c.date_time,
        query_sub_verify.c.verify_project,
        query_sub_verify.c.verify_department,
        query_sub_verify.c.verify_type,
        query_sub_verify.c.verify_dh
    ).union_all(db.session.query(
        query_sub_verify_sample.c.id,
        query_sub_verify_sample.c.品名,
        query_sub_verify_sample.c.简拼,
        query_sub_verify_sample.c.产地,
        query_sub_verify_sample.c.规格,
        query_sub_verify_sample.c.批号,
        query_sub_verify_sample.c.verify_number,
        query_sub_verify_sample.c.单位,
        query_sub_verify_sample.c.date_time,
        query_sub_verify_sample.c.verify_project,
        query_sub_verify_sample.c.verify_department,
        query_sub_verify_sample.c.verify_type,
        query_sub_verify_sample.c.verify_dh
    )).layui_paginate()
    return table_api(
        data=[{
            'id': item.id,
            '品名': item.品名,
            '简拼': item.简拼,
            '产地': item.产地,
            '规格': item.规格,
            '批号': item.批号,
            'verify_number_sum':item.verify_number_sum,
            'data_label': item.data_label,
            'date_time':item.date_time,
            'verify_project': item.verify_project,
            'verify_department': item.verify_department,
            'verify_type': item.verify_type,
            'verify_dh': item.verify_dh
        } for item in query.items],
        count=query.total)


@bp.get('/verify/yp/add')
@authorize("store:raw:verify:add:yp", log=False)
def verify_add():
    '''先获取合格的供应商'''

    return render_template('store/raw/yp/verify/add.html')


#   用户分页查询
@bp.get('/verify/yp/searchdata')
@authorize("store:raw:verify:add:yp")
def verify_searchdata():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    批号 = str_escape(request.args.get('批号', type=str))
    verify_type=str_escape(request.args.get('verify_type', type=str))
    print('verify_type',verify_type)


    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 批号:
        filters.append(StoreRawYPModel.批号.contains(批号))
    if verify_type == '临期请验':
        query = db.session.query(
            RawYPModel,
            StoreRawYPModel,
            StoreRawYPModel_location.stock_number,
            StoreRawYPModel_location.id,
            StoreRawYPModel_location.location_id,
            LocationRawYP.name
        ).outerjoin(RawYPModel,StoreRawYPModel.raw_id==RawYPModel.id
        ).outerjoin(StoreRawYPModel_location,StoreRawYPModel.id==StoreRawYPModel_location.store_id
        ).outerjoin(LocationRawYP,StoreRawYPModel_location.location_id==LocationRawYP.id).filter(*filters).layui_paginate()
    elif verify_type == '正常请验':
        subquery = db.session.query(VerifyRawYPModel.store_location_id
                                    ).filter(VerifyRawYPModel.verify_type == '正常请验'
                                             ).subquery()

        # 使用子查询查询原料退货

        query = db.session.query(
            RawYPModel,
            StoreRawYPModel,
            StoreRawYPModel_location.stock_number,
            StoreRawYPModel_location.id,
            StoreRawYPModel_location.location_id,
            LocationRawYP.name
        ).outerjoin(RawYPModel, StoreRawYPModel.raw_id == RawYPModel.id
                    ).outerjoin(StoreRawYPModel_location, StoreRawYPModel.id == StoreRawYPModel_location.store_id
                                ).outerjoin(LocationRawYP,
                                            StoreRawYPModel_location.location_id == LocationRawYP.id).filter(
            *filters).filter(~StoreRawYPModel_location.id.in_(subquery)).layui_paginate()
    elif verify_type == '小样请验':
        query = db.session.query(
            RawYPModel
        ).filter(
            RawYPModel.品名.contains(品名),RawYPModel.状态==1).layui_paginate()
        return table_api(
            data=[{
                'id': item.id,
                '品名': item.品名,
                '简拼': item.简拼,
                '产地': item.默认产地,
                '规格': item.默认规格,
                '代码':item.代码,
                '单位': 'Kg',
                '单据日期': item.update_at,
                'location_name': item.执行标准

            } for item in query.items],
            count=query.total)
    return table_api(
        data=[{
            'id': item.id,
            '品名': item.RawYPModel.品名,
            '简拼': item.RawYPModel.简拼,
            '产地': item.StoreRawYPModel.产地,
            '规格': item.StoreRawYPModel.规格,
            '批号': item.StoreRawYPModel.批号,
            '单位':item.StoreRawYPModel.unit.data_label,
            '库存量': item.stock_number,
            '单据日期': item.StoreRawYPModel.date_time,
            'location_id': item.location_id,
            'location_name':item.name

        } for item in query.items],
        count=query.total)

    # 用户增加
@bp.get('/verify/yp/add/frm_table')
@authorize("store:raw:verify:add:yp", log=False)
def add_verify_frm_table():
    '''先获取合格的供应商'''
    searchtext=request.args.get('searchtext')
    verify_type = request.args.get('verify_type')
    return render_template('store/raw/yp/verify/add_frm_table.html',info={'searchtext':searchtext,'verify_type':verify_type})

@bp.post('/verify/yp/save')
@authorize("store:raw:verify:add:yp", log=True)
def verify_save():
    req_json = request.get_json(force=True)

    fileds=['date_time' ,'user_verifyer_id','remark','verify_project','verify_department','verify_type']
    row={}
    for filed in fileds:
        if filed in ['date_time','user_verifyer_id']:
            if not req_json.get('field').get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get('field').get(filed):
            row[filed]=str_escape(req_json.get('field').get(filed))
    if row.get('verify_project')=='部分检验':
        if not row.get('remark'):
            return fail_api(msg=f"请把部分检验的项目写在备注里面，备注不得为空")
    if row.get('verify_type')=='小样请验':

        row['verify_dh'] = get_djbh('请验管理_yl_yp_sample', req_json.get('field').get('date_time'))  # 'CK_TH_2020_10_0006'
    else:
        row['verify_dh'] =get_djbh('请验管理_yl_yp',req_json.get('field').get('date_time'))   #'CK_TH_2020_10_0006'

    try:
        isHaveDate=False
        if row.get('verify_type') == '小样请验':
            for item in req_json.get('table_datas'):
                if item.get('id'):
                    产地 =item.get('产地')
                    规格 = item.get('规格')
                    批号 = item.get('批号')
                    单位 = item.get('单位')
                    verify_number = Decimal(item.get('库存量'))
                    raw_id = item.get('id')
                    pro = VerifyRawYPModelSample(**row, verify_number=verify_number, raw_id=raw_id
                                           ,产地=产地,规格=规格,批号=批号,单位=单位
                                           )
                    db.session.add(pro)

                    isHaveDate = True
        else:
            for item in req_json.get('table_datas'):
                if item.get('id'):
                    verify_number = Decimal(item.get('库存量'))
                    store_location_id = item.get('id')
                    pro = VerifyRawYPModel(**row, verify_number=verify_number, store_location_id=store_location_id)
                    db.session.add(pro)

                    isHaveDate = True
        if isHaveDate:
            db.session.commit()
        else:
            return fail_api(msg=f"发生错误:没有数据")

    except Exception as e:
        print(e)
        return fail_api(msg=f"发生错误:{e}")
    return success_api(msg="增加成功")
# 删除请验数据
@bp.delete('/verify/yp/remove/<string:verify_dh>')
@authorize("store:raw:verify:remove:yp", log=True)
def verify_delete(verify_dh):
    if 'SP' in verify_dh:
        qurey = VerifyRawYPModelSample.query.filter_by(verify_dh=verify_dh).all()
    else:
        qurey = VerifyRawYPModel.query.filter_by(verify_dh=verify_dh).all()
    for item in qurey:
        db.session.delete(item)

    try:
        db.session.commit()
    except Exception as e:
        print(e)
        return fail_api(msg=f"删除失败:{e}")


    return success_api(msg="删除成功")

#  编辑用户
@bp.get('/verify/yp/edit/<string:dh>')
@authorize("store:raw:verify:edit:yp", log=True)
def verify_edit(dh):
    if 'SP' in dh:
        results = db.session.query(VerifyRawYPModelSample
                                   ).filter(VerifyRawYPModelSample.verify_dh==dh).all()
        info={}
        info['date_time'] = results[0].date_time
        info['verify_dh'] = dh
        info['remark'] = results[0].remark
        info['verify_project'] = results[0].verify_project
        info['verify_department'] = results[0].verify_department
        info['verify_type'] = results[0].verify_type
        user_verifyer= results[0].user_verifyer
        rowdatas=[]
        for result in results:

            rowdata={

                '库存量':result.verify_number,
                'verify_id': result.id,
                'id':result.raw_id,
                '品名': result.raw.品名,
                '简拼': result.raw.简拼,
                '产地':  result.产地,
                '规格': result.规格,
                '批号': result.批号,
                '单位': result.单位,
                '执行标准': result.raw.执行标准
            }
            rowdatas.append(rowdata)
    else:
        results = db.session.query(VerifyRawYPModel
                                   ).filter(VerifyRawYPModel.verify_dh == dh).all()
        info = {}
        info['date_time'] = results[0].date_time
        info['verify_dh'] = dh
        info['remark'] = results[0].remark
        info['verify_project'] = results[0].verify_project
        info['verify_department'] = results[0].verify_department
        info['verify_type'] = results[0].verify_type
        user_verifyer = results[0].user_verifyer
        rowdatas = []
        for result in results:
            rowdata = {

                '库存量': result.verify_number,
                'verify_id': result.id,
                'id': result.store_location_id,
                '品名': result.store_location.storeRawYp.raw.品名,
                '简拼': result.store_location.storeRawYp.raw.简拼,
                '产地': result.store_location.storeRawYp.产地,
                '规格': result.store_location.storeRawYp.规格,
                '批号': result.store_location.storeRawYp.批号,
                '单位': result.store_location.storeRawYp.unit.data_label,

                'location_id': result.store_location.location_id,
                'location_name': result.store_location.location.name,
            }
            rowdatas.append(rowdata)


    return render_template('store/raw/yp/verify/edit.html',results=results,info=info,user_verifyer=user_verifyer,rowdatas=rowdatas)

#  编辑用户
@bp.put('/verify/yp/update')
@authorize("store:raw:verify:edit:yp", log=True)
def verify_update():
    '''1.先检查是否有为空的时候
        2.验证 小样 只能有一个；其他 至少有一个之前的批号的要不然请验的品种和批号就对不上了，一般都是加量的时候做的
        3.更新操作
    '''

    req_json = request.get_json(force=True)

    verify_dh = str_escape(req_json.get('field').get('verify_dh'))
    fileds = ['verify_dh','date_time', 'user_verifyer_id', 'remark', 'verify_project', 'verify_department', 'verify_type']
    row = {}
    for filed in fileds:
        if filed in ['date_time', 'user_verifyer_id']:
            if not req_json.get('field').get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get('field').get(filed):
            row[filed] = str_escape(req_json.get('field').get(filed))
    if row.get('verify_project') == '部分检验':
        if not row.get('remark'):
            return fail_api(msg=f"请把部分检验的项目写在备注里面，备注不得为空")
    if req_json.get('field').get('verify_type') == '小样请验':
        pass

    row['update_at'] = datetime.datetime.now()

    try:

        isHaveDate = False
        if row.get('verify_type') == '小样请验':
            qurey = VerifyRawYPModelSample.query.filter_by(verify_dh=verify_dh).all()
            for item in qurey:
                db.session.delete(item)

            for item in req_json.get('table_datas'):
                if item.get('id'):
                    产地 = item.get('产地')
                    规格 = item.get('规格')
                    批号 = item.get('批号')
                    单位 = item.get('单位')
                    verify_number = Decimal(item.get('库存量'))
                    raw_id = item.get('id')
                    pro = VerifyRawYPModelSample(**row, verify_number=verify_number, raw_id=raw_id
                                                 , 产地=产地, 规格=规格, 批号=批号, 单位=单位
                                                 )
                    db.session.add(pro)

                    isHaveDate = True
        else:
            qurey =VerifyRawYPModel.query.filter_by(verify_dh=verify_dh).all()
            for item in qurey:
                db.session.delete(item)
            for item in req_json.get('table_datas'):
                if item.get('id'):
                    verify_number = Decimal(item.get('库存量'))
                    store_location_id = item.get('id')
                    pro = VerifyRawYPModel(**row, verify_number=verify_number, store_location_id=store_location_id)
                    db.session.add(pro)
                    isHaveDate = True
        if isHaveDate:
            db.session.commit()
        else:
            return fail_api(msg=f"发生错误:没有数据")
    except Exception as e :
        print(e)
        return fail_api(msg=f"更新失败,{e}")

    return success_api(msg="更新成功")


# 取样
@bp.get('/sample/yp/')
@authorize("store:raw:sample:main:yp")
def sample_main():
    return render_template('store/raw/yp/sample/main.html')


#   用户分页查询
# @bp.get('/sample/yp/data')
# # @authorize("store:raw:sample:main:yp")
@bp.get('/sample/yp/data')
def sample_data():


    # 获取请求参数
    sql="""select * from (select * from 原料_yp_取样 union all select * from 原料_yp_取样_小样) as t1 """
    localfileds={
        'sample_number':'取样数量'
    }
    print(sql)
    return query_one(sql,localfileds)
    品名 = str_escape(request.args.get('品名', type=str))
    简拼 = str_escape(request.args.get('简拼', type=str))
    print(request.args.get('contions'))
    if request.args.get('contions'):

        品名=json.loads(request.args.get('contions')).get('seach_content')
    t0=db.session.query(SampleRawYPModel,VerifyRawYPModel.store_location_id,VerifyRawYPModel.verify_number,VerifyRawYPModel.verify_project).outerjoin(VerifyRawYPModel,SampleRawYPModel.verify_id==VerifyRawYPModel.id).subquery()
    t1 = db.session.query(t0, StoreRawYPModel_location.store_id).outerjoin(StoreRawYPModel_location,
                                                                                          t0.c.store_location_id == StoreRawYPModel_location.id).subquery()
    t2=db.session.query(t1,StoreRawYPModel.产地
                        ,StoreRawYPModel.规格
                        ,StoreRawYPModel.批号
                        ,StoreRawYPModel.unit_id
                        ,StoreRawYPModel.raw_id
                        ).outerjoin(StoreRawYPModel,t1.c.store_id==StoreRawYPModel.id).subquery()
    t3=db.session.query(t2,case(
                            (DictData.data_label=='Kg',DictData.data_label),
                             (DictData.data_label == '条', DictData.data_label),
                             (DictData.data_label == '个', DictData.data_label),
                             else_='Kg'
                         ).label('data_label'),DictData.data_rel

                        ).outerjoin(DictData,t2.c.unit_id==DictData.id).subquery()
    t4=db.session.query(t3,RawYPModel.品名
                        ,RawYPModel.简拼
                        ).outerjoin(RawYPModel,t3.c.raw_id==RawYPModel.id).subquery()

    query_sub_sample=db.session.query(t4,db.func.sum(t4.c.sample_number * t4.c.data_rel).label('sample_number_sum')
                                      ,db.func.sum(t4.c.verify_number * t4.c.data_rel).label('verify_number_sum')
                        ).group_by(t4.c.sample_dh).filter(t4.c.品名.contains(品名),t4.c.简拼.contains(简拼)).subquery()

    ta_0 = db.session.query(SampleRawYPModelSample,SampleRawYPModelSample.sample_number.label('sample_number_sum') ,
                            VerifyRawYPModelSample.raw_id, VerifyRawYPModelSample.verify_number.label('verify_number_sum'),
                          VerifyRawYPModelSample.verify_project,VerifyRawYPModelSample.单位.label('data_label'),
                            VerifyRawYPModelSample.产地,VerifyRawYPModelSample.规格,
                            VerifyRawYPModelSample.批号
                            ).outerjoin(VerifyRawYPModelSample,
                                                                     SampleRawYPModelSample.verify_id == VerifyRawYPModelSample.id).subquery()
    ta_1=db.session.query(ta_0,RawYPModel.品名,RawYPModel.简拼).outerjoin(RawYPModel,ta_0.c.raw_id==RawYPModel.id).subquery()
    query_sub_sample_sample = db.session.query(
        ta_1
    ).filter(ta_1.c.品名.contains(品名),ta_1.c.简拼.contains(简拼)).subquery()
    query_all=db.session.query(
        query_sub_sample.c.id,
        query_sub_sample.c.品名,
        query_sub_sample.c.简拼,
        query_sub_sample.c.产地,
        query_sub_sample.c.规格,
        query_sub_sample.c.批号,
        query_sub_sample.c.sample_number_sum,
        query_sub_sample.c.verify_number_sum,
        query_sub_sample.c.verify_project,
        query_sub_sample.c.data_label,
        query_sub_sample.c.date_time,
        query_sub_sample.c.sample_project,
        query_sub_sample.c.sample_department,
        query_sub_sample.c.sample_type,
        query_sub_sample.c.sample_dh
    ).union_all(db.session.query(
        query_sub_sample_sample.c.id,
        query_sub_sample_sample.c.品名,
        query_sub_sample_sample.c.简拼,
        query_sub_sample_sample.c.产地,
        query_sub_sample_sample.c.规格,
        query_sub_sample_sample.c.批号,
        query_sub_sample_sample.c.sample_number_sum,
        query_sub_sample_sample.c.verify_number_sum,
        query_sub_sample_sample.c.verify_project,
        query_sub_sample_sample.c.data_label,
        query_sub_sample_sample.c.date_time,
        query_sub_sample_sample.c.sample_project,
        query_sub_sample_sample.c.sample_department,
        query_sub_sample_sample.c.sample_type,
        query_sub_sample_sample.c.sample_dh
    ))
    # #layui_paginate
    paginated_result = query_all.layui_paginate()


    # # 获取字段名称和类型
    # for column in query_all.columns:
    #     column_name = column.name
    #     column_type = type(column.type).__name__
    #     print(f"字段名: {column_name.split(')s_')[1]}, 字段类型: {column_type}")
    # query_all=db.session.query(query_all).layui_paginate()
    for item in paginated_result.items:
        print(item._mapping)
    return table_api(
        data=[dict(item._mapping) for item in paginated_result.items],
        count=paginated_result.total, msg='成功',fileds=[ {'value' : column['name'],'text':column['name'],
        'column_type' : str(column['type'])} for column in query_all.column_descriptions])
    return table_api(
        data=[{
            'id': item.id,

            '品名': item.品名,
            '简拼': item.简拼,
            '产地': item.产地,
            '规格': item.规格,
            '批号': item.批号,
            'sample_number_sum':item.sample_number_sum,
            'verify_number_sum': item.verify_number_sum,
            'data_label': item.data_label,
            'date_time':item.date_time,
            'sample_project': item.sample_project,
            'verify_project': item.verify_project,
            'sample_department': item.sample_department,
            'sample_type': item.sample_type,
            'sample_dh': item.sample_dh
        } for item in query_all.items],
        count=query_all.total,msg='成功')

@bp.get('/sample/yp/add')
@authorize("store:raw:sample:add:yp", log=False)
def sample_add():
    '''先获取合格的供应商'''

    return render_template('store/raw/yp/sample/add.html')

#   用户分页查询
@bp.get('/sample/yp/searchdata')
@authorize("store:raw:sample:add:yp")
def sample_searchdata():
    # 获取请求参数
    品名 = str_escape(request.args.get('品名', type=str))
    批号 = str_escape(request.args.get('批号', type=str))
    sample_type=str_escape(request.args.get('sample_type', type=str))
    print('verify_type',sample_type)


    filters = []
    if 品名:
        filters.append(RawYPModel.品名.contains(品名))

    if 批号:
        filters.append(StoreRawYPModel.批号.contains(批号))
    if sample_type == '临期取样':

        subquery = db.session.query(SampleRawYPModel.verify_id
                                    ).subquery()
        # 使用子查询查询原料退货
        query = db.session.query(
            VerifyRawYPModel
        ).filter(~VerifyRawYPModel.id.in_(subquery),VerifyRawYPModel.verify_type=='临期请验').filter(*filters).layui_paginate()
    elif sample_type == '正常取样':
        subquery = db.session.query(SampleRawYPModel.verify_id
                                    ).subquery()
        # 使用子查询查询原料退货
        query = db.session.query(
            VerifyRawYPModel
        ).filter(~VerifyRawYPModel.id.in_(subquery),VerifyRawYPModel.verify_type=='正常请验').filter(*filters).layui_paginate()
    elif sample_type == '小样取样':
        subquery = db.session.query(SampleRawYPModelSample.verify_id
                                    ).subquery()
        # 使用子查询查询原料退货
        query = db.session.query(
            VerifyRawYPModelSample
        ).filter(~VerifyRawYPModelSample.id.in_(subquery), VerifyRawYPModelSample.verify_type == '小样请验').filter(
            *filters).layui_paginate()

        return table_api(
            data=[{
                'id': item.id,
                'verify_id': item.id,
                '品名': item.raw.品名,
                '简拼': item.raw.简拼,
                '产地': item.产地,
                '规格': item.规格,
                '代码':item.raw.代码,
                '批号': item.批号,
                '单位': item.单位,
                '请验量': item.verify_number,
                '单据日期': item.date_time,
                'location_id': '',
                'location_name': item.raw.执行标准,
                'verify_dh': item.verify_dh

            } for item in query.items],
            count=query.total)
    return table_api(
        data=[{
            'id': item.id,
            'verify_id': item.id,
            '品名': item.store_location.storeRawYp.raw.品名,
            '简拼': item.store_location.storeRawYp.raw.简拼,
            '产地': item.store_location.storeRawYp.产地,
            '规格': item.store_location.storeRawYp.规格,
            '批号': item.store_location.storeRawYp.批号,
            '单位':item.store_location.storeRawYp.unit.data_label,
            '请验量': item.verify_number,
            '单据日期': item.date_time,
            'location_id': item.store_location.location_id,
            'location_name':item.store_location.location.name,
            'verify_dh': item.verify_dh

        } for item in query.items],
        count=query.total)

    # 用户增加
@bp.get('/sample/yp/add/frm_table')
@authorize("store:raw:sample:add:yp", log=False)
def add_sample_frm_table():
    ''''''
    searchtext=request.args.get('searchtext')
    sample_type = request.args.get('sample_type')
    return render_template('store/raw/yp/sample/add_frm_table.html',info={'searchtext':searchtext,'sample_type':sample_type})

@bp.post('/sample/yp/save')
@authorize("store:raw:sample:add:yp", log=True)
def sample_save():
    req_json = request.get_json(force=True)

    fileds=['date_time' ,'user_sampler_id','remark','sample_project','sample_department','sample_type']
    row={}
    for filed in fileds:
        if filed in ['date_time','user_sampler_id']:
            if not req_json.get('field').get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get('field').get(filed):
            row[filed]=str_escape(req_json.get('field').get(filed))

    if row.get('sample_type')=='小样取样':

        row['sample_dh'] = get_djbh('取样管理_yl_yp_sample', req_json.get('field').get('date_time'))  # 'CK_TH_2020_10_0006'
    else:
        row['sample_dh'] =get_djbh('取样管理_yl_yp',req_json.get('field').get('date_time'))   #'CK_TH_2020_10_0006'

    try:
        isHaveDate=False
        if row.get('sample_type') == '小样取样':
            for item in req_json.get('table_datas'):
                if item.get('id'):

                    sample_number = Decimal(item.get('sample_number'))
                    verify_id = item.get('id')
                    pro = SampleRawYPModelSample(**row, sample_number=sample_number, verify_id=verify_id)
                    db.session.add(pro)

                    isHaveDate = True
        else:
            for item in req_json.get('table_datas'):
                if item.get('id'):
                    sample_number = Decimal(item.get('sample_number'))
                    verify_id = item.get('id')
                    pro = SampleRawYPModel(**row, sample_number=sample_number, verify_id=verify_id)
                    db.session.add(pro)
                    #扣除取样量
                    verify_item = VerifyRawYPModel.query.filter_by(id=item.get('id')).first()
                    location_item=verify_item.store_location
                    location_item.stock_number = location_item.stock_number - sample_number
                    db.session.add(location_item)

                    isHaveDate = True
        if isHaveDate:
            db.session.commit()
        else:
            return fail_api(msg=f"发生错误:没有数据")

    except Exception as e:
        print(e)
        return fail_api(msg=f"发生错误:{e}")
    return success_api(msg="增加成功")

# 删除取样数据
@bp.delete('/sample/yp/remove/<string:sample_dh>')
@authorize("store:raw:sample:remove:yp", log=True)
def sample_delete(sample_dh):
    if 'SP_SP' in sample_dh:
        qurey = SampleRawYPModelSample.query.filter_by(sample_dh=sample_dh).all()
        for item in qurey:
            db.session.delete(item)
    else:
        qurey = SampleRawYPModel.query.filter_by(sample_dh=sample_dh).all()
        for item in qurey:
            location = item.verify.store_location
            location.stock_number = location.stock_number + item.sample_number
            db.session.add(location)

            db.session.delete(item)

    try:
        db.session.commit()
    except Exception as e:
        print(e)
        return fail_api(msg=f"删除失败:{e}")


    return success_api(msg="删除成功")



#  编辑取样
@bp.get('/sample/yp/edit/<string:dh>')
@authorize("store:raw:sample:edit:yp", log=True)
def sample_edit(dh):
    if 'SP_SP' in dh:
        results = db.session.query(SampleRawYPModelSample
                                   ).filter(SampleRawYPModelSample.sample_dh==dh).all()
        info={}
        info['date_time'] = results[0].date_time
        info['sample_dh'] = dh
        info['remark'] = results[0].remark
        info['sample_project'] = results[0].sample_project
        info['sample_department'] = results[0].sample_department
        info['sample_type'] = results[0].sample_type
        user_sampler= results[0].user_sampler
        rowdatas=[]
        for result in results:

            rowdata={

                'sample_number':result.sample_number,
                'id': result.verify_id,
                'verify_dh': result.verify.verify_dh,
                '品名': result.verify.raw.品名,
                '简拼': result.verify.raw.简拼,
                '产地':  result.verify.产地,
                '规格': result.verify.规格,
                '批号': result.verify.批号,
                '单位': result.verify.单位,
                '请验量': result.verify.verify_number,

                'location_name': result.verify.raw.执行标准,
            }
            rowdatas.append(rowdata)
    else:
        results = db.session.query(SampleRawYPModel
                                   ).filter(SampleRawYPModel.sample_dh == dh).all()
        info = {}
        info['date_time'] = results[0].date_time
        info['sample_dh'] = dh
        info['remark'] = results[0].remark
        info['sample_project'] = results[0].sample_project
        info['sample_department'] = results[0].sample_department
        info['sample_type'] = results[0].sample_type
        user_sampler = results[0].user_sampler
        rowdatas = []
        for result in results:
            rowdata = {

                'sample_number': result.sample_number,
                'id': result.verify_id,
                'verify_dh': result.verify.verify_dh,

                '品名': result.verify.store_location.storeRawYp.raw.品名,
                '简拼': result.verify.store_location.storeRawYp.raw.简拼,
                '产地': result.verify.store_location.storeRawYp.产地,
                '规格': result.verify.store_location.storeRawYp.规格,
                '批号': result.verify.store_location.storeRawYp.批号,
                '单位': result.verify.store_location.storeRawYp.unit.data_label,
                '请验量': result.verify.verify_number,
                'location_id': result.verify.store_location.location_id,
                'location_name': result.verify.store_location.location.name,
            }
            rowdatas.append(rowdata)


    return render_template('store/raw/yp/sample/edit.html',results=results,info=info,user_sampler=user_sampler,rowdatas=rowdatas)


#  编辑用户
@bp.put('/sample/yp/update')
@authorize("store:raw:sample:edit:yp", log=True)
def sample_update():
    '''1.先检查是否有为空的时候
        2.验证 小样 只能有一个；其他 至少有一个之前的批号的要不然请验的品种和批号就对不上了，一般都是加量的时候做的
        3.更新操作
    '''

    req_json = request.get_json(force=True)

    sample_dh = str_escape(req_json.get('field').get('sample_dh'))
    fileds = ['date_time', 'user_sampler_id', 'remark', 'sample_project', 'sample_department', 'sample_type']
    row = {}
    for filed in fileds:
        if filed in ['date_time', 'user_sampler_id']:
            if not req_json.get('field').get(filed):
                return fail_api(msg=f"{filed}不得为空")
        if req_json.get('field').get(filed):
            row[filed] = str_escape(req_json.get('field').get(filed))



    row['update_at'] = datetime.datetime.now()

    try:

        isHaveDate = False
        if row.get('sample_type') == '小样取样':
            qurey = SampleRawYPModelSample.query.filter_by(sample_dh=sample_dh).all()
            for item in qurey:
                db.session.delete(item)

            for item in req_json.get('table_datas'):
                if item.get('id'):
                    sample_number = Decimal(item.get('sample_number'))
                    verify_id = item.get('id')
                    pro = SampleRawYPModelSample(**row, sample_number=sample_number, verify_id=verify_id,sample_dh=sample_dh)
                    db.session.add(pro)

                    isHaveDate = True
        else:
            qurey =SampleRawYPModel.query.filter_by(sample_dh=sample_dh).all()
            for item in qurey:
                # 扣除取样量
                verify_item = VerifyRawYPModel.query.filter_by(id=item.verify_id).first()
                location_item = verify_item.store_location
                location_item.stock_number = location_item.stock_number + item.sample_number
                db.session.add(location_item)
                db.session.delete(item)
            for item in req_json.get('table_datas'):
                if item.get('id'):
                    sample_number = Decimal(item.get('sample_number'))
                    verify_id = item.get('id')
                    pro = SampleRawYPModel(**row, sample_number=sample_number, verify_id=verify_id, sample_dh=sample_dh)
                    db.session.add(pro)
                    # 扣除取样量
                    verify_item = VerifyRawYPModel.query.filter_by(id=item.get('id')).first()
                    location_item = verify_item.store_location
                    location_item.stock_number = location_item.stock_number - sample_number
                    db.session.add(location_item)
                    isHaveDate = True
        if isHaveDate:
            db.session.commit()
        else:
            return fail_api(msg=f"发生错误:没有数据")
    except Exception as e :
        print(e)
        return fail_api(msg=f"更新失败,{e}")

    return success_api(msg="更新成功")